/*
 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
 *
 * Copyright (c) 2013-2014 jfinal app. jfapp Group.
 */

package app.models.game;

import app.dtos.GameWinGift;
import com.github.sog.annotation.TableBind;
import com.github.sog.config.StringPool;
import com.github.sog.controller.datatables.core.ColumnDef;
import com.github.sog.controller.datatables.core.DatatablesCriterias;
import com.github.sog.plugin.sqlinxml.SqlKit;
import com.google.common.base.Strings;
import com.google.common.collect.Lists;
import com.jfinal.plugin.activerecord.*;

import java.util.List;

/**
 * <p>
 * The table ol_game_joinrecord mapping model.
 * </p>
 */
@TableBind(tableName = "ol_game_joinrecord")
public class GameJoinrecord extends Model<GameJoinrecord> {

    private static final long   serialVersionUID = 1L;
    /**
     * The public dao.
     */
    public static final GameJoinrecord dao = new GameJoinrecord();


    public List<GameJoinrecord> list(){
    	return find(SqlKit.sql("gamejoinrecord.list"));
    }

    public int findDayPlayTimes(int gameId, long memberId, String day) {
        int count = Db.queryNumber(SqlKit.sql("gamejoinrecord.findDayPlayTimes"), gameId, memberId, day).intValue();
        return count;
    }

    /**
     * 根据mid查询获奖的记录信息
     * @param mid 会员id
     */
    public List<GameJoinrecord> findWinRecordByMid(String mid) {
        List<GameJoinrecord> list = find(SqlKit.sql("gamejoinrecord.findRecordByMid")+SqlKit.sql("gamejoinrecord.findRecordByMid-win-where"), mid);
        return list;
    }

    public List<GameJoinrecord> findRecordByMid(String mid) {
        List<GameJoinrecord> list = find(SqlKit.sql("gamejoinrecord.findRecordByMid")+SqlKit.sql("gamejoinrecord.findRecordByMid-win-where"), mid);
        return list;
    }

    public Page<GameJoinrecord> dateGameJoin(DatatablesCriterias criterias, String date, int game) {
        int pageSize = criterias.getDisplaySize();
        int start = criterias.getDisplayStart() / pageSize + 1;
        final List<ColumnDef> sortingColumnDefs = criterias.getSortingColumnDefs();
        String where = " FROM ol_game_joinrecord record," +
                "ol_game game WHERE record.game=game.id ";
        List<Object> params = Lists.newArrayList();
        if (!Strings.isNullOrEmpty(date)) {
            where = where + " AND record.join_date=?";
            params.add(date);
        }
        if (game != 0) {
            where = where + " AND record.game=?";
            params.add(game);
        }
        if (sortingColumnDefs != null && !sortingColumnDefs.isEmpty()) {
            StringBuilder orderBy = new StringBuilder();
            for (ColumnDef sortingColumnDef : sortingColumnDefs)
                if (sortingColumnDef.isSortable()) {
                    orderBy.append(sortingColumnDef.getName()).append(StringPool.SPACE).append(sortingColumnDef.getSortDirection().toString());
                }
            // todo mulite column.
            final String byColumns = orderBy.toString();
            if(!Strings.isNullOrEmpty(byColumns)){
                where += " ORDER BY " + byColumns;
            }
        }
        where += " GROUP BY record.game, record.join_date, game.name";
        if (!where.contains("ORDER")) {
            where += " ORDER BY record.game DESC";
        }

        return paginate(start, pageSize, SqlKit.sql("gamejoinrecord.dateGameJoin"), where, params.toArray());
    }

    public List<GameWinGift> gameWinGift(String query, String sn) {
        String sql = SqlKit.sql("gamejoinrecord.gameWinGift");
        List<Object> params = Lists.newArrayList();
        params.add(sn);

        if (!Strings.isNullOrEmpty(query)) {
            sql += "AND (member.phone=? OR member.card_no=?)";
            params.add(query);
            params.add(query);
        }

        List<Record> list = Db.find(sql, params.toArray());
        List<GameWinGift> gameWinGifts = Lists.newArrayList();
        if (list != null && !list.isEmpty()) {
            for (Record r : list) {
                GameWinGift g = new GameWinGift();

                g.recordId = r.getNumber("recordId") != null ? r.getNumber("recordId").longValue() : 0;

                g.memberId = r.getNumber("memberId") != null ? r.getNumber("recordId").longValue() : 0;
                g.memberName = r.getStr("memberName");
                g.joinTime = r.getTimestamp("joinTime");
                g.recv = r.getBoolean("recv") != null ? r.getBoolean("recv") : false;
                g.recvTime = r.getTimestamp("recvTime");
                g.gameId = r.getNumber("gameId") != null ? r.getNumber("gameId").longValue() : 0;
                g.gameName = r.getStr("gameName");
                g.awardsId = r.getNumber("awardsId") != null ? r.getNumber("awardsId").longValue() : 0;
                g.awardsName = r.getStr("awardsName");
                g.giftId = r.getNumber("giftId") != null ? r.getNumber("giftId").longValue() : 0;
                g.giftName = r.getStr("giftName");

                gameWinGifts.add(g);
            }
        }
        return gameWinGifts;
    }

    public Page<GameJoinrecord> detailList(DatatablesCriterias criterias, String date, int gameId, String query, Boolean winning) {
        int pageSize = criterias.getDisplaySize();
        int start = criterias.getDisplayStart() / pageSize + 1;
        final List<ColumnDef> sortingColumnDefs = criterias.getSortingColumnDefs();
        String where = " FROM ol_game_joinrecord record, ol_member member" +
                " WHERE record.member=member.id AND record.join_date = ? AND record.game = ? ";
        List<Object> params = Lists.newArrayList();
        params.add(date);
        params.add(gameId);

        if (!Strings.isNullOrEmpty(query)) {
            where = where + " AND (member.card_no=? OR member.phone=?)";
            params.add(query);
            params.add(query);
        }
        if (winning != null) {
            where = where + " AND record.winning=?";
            params.add(winning);
        }
        if (sortingColumnDefs != null && !sortingColumnDefs.isEmpty()) {
            StringBuilder orderBy = new StringBuilder();
            for (ColumnDef sortingColumnDef : sortingColumnDefs)
                if (sortingColumnDef.isSortable()) {
                    orderBy.append(sortingColumnDef.getName()).append(StringPool.SPACE).append(sortingColumnDef.getSortDirection().toString());
                }
            // todo mulite column.
            final String byColumns = orderBy.toString();
            if(!Strings.isNullOrEmpty(byColumns)){
                where += " ORDER BY " + byColumns;
            }
        }
        if (!where.contains("ORDER")) {
            where += " ORDER BY record.join_time DESC";
        }

        return paginate(start, pageSize, SqlKit.sql("gamejoinrecord.detailList"), where, params.toArray());
    }

    /// ------



    public Record joinStat(Game game, String start, String end) {
        Integer gameId = game.getInt("id");

        Record record = joinStat(gameId, start, end);

        record.set("gameId", gameId);
        record.set("gameName", game.get("name"));
        return record;
    }

    public Record joinStat(int gameId, String start, String end) {
        Number totalTime = Db.queryNumber(SqlKit.sql("gamejoinrecord.totalTime"), start, end, gameId);
        Number totalPeople = Db.queryNumber(SqlKit.sql("gamejoinrecord.totalPeople"), start, end, gameId);
        Number winingTime = Db.queryNumber(SqlKit.sql("gamejoinrecord.winingTime"), start, end, gameId);
        Number winingPeople = Db.queryNumber(SqlKit.sql("gamejoinrecord.winingPeople"), start, end, gameId);

        Record record = new Record();
        record.set("totalTime", totalTime);
        record.set("totalPeople", totalPeople);
        record.set("winingTime", winingTime);
        record.set("winingPeople", winingPeople);
        return record;
    }

    public Page<Record> joinStatList(DatatablesCriterias criterias, String start, String end, int game, Boolean winning, String gift) {
        int pageSize = criterias.getDisplaySize();
        int pageNumber = criterias.getDisplayStart() / pageSize + 1;

        final List<ColumnDef> sortingColumnDefs = criterias.getSortingColumnDefs();

        String select = SqlKit.sql("gamejoinrecord.select");
        String where = SqlKit.sql("gamejoinrecord.where");

        List<Object> params = Lists.newArrayList();

        if (!Strings.isNullOrEmpty(start)) {
            where += " AND r.join_date>=?";
            params.add(start);
        }

        if (!Strings.isNullOrEmpty(end)) {
            where += " AND r.join_date<=?";
            params.add(end);
        }

        if (game != 0) {
            where += " AND r.game=?";
            params.add(game);
        }

        if (winning != null) {
            where += " AND r.winning=?";
            params.add(winning);
        }

        if (!Strings.isNullOrEmpty(gift)) {
            where += " AND r.gift_name like ?";
            params.add(StringPool.PERCENT + gift + StringPool.PERCENT);
        }

        if (sortingColumnDefs != null && !sortingColumnDefs.isEmpty()) {
            StringBuilder orderBy = new StringBuilder();
            for (ColumnDef sortingColumnDef : sortingColumnDefs)
                if (sortingColumnDef.isSortable()) {
                    orderBy.append(sortingColumnDef.getName()).append(StringPool.SPACE).append(sortingColumnDef.getSortDirection().toString());
                }
            // todo mulite column.
            final String byColumns = orderBy.toString();
            if(!Strings.isNullOrEmpty(byColumns)){
                where += " ORDER BY " + byColumns;
            }
        }
        if (!where.contains("ORDER")) {
            where += " ORDER BY r.join_time DESC";
        }
        if (params.isEmpty()) {
            return Db.paginate(pageNumber, pageSize, select, where);
        }
        return Db.paginate(pageNumber, pageSize, select, where, params.toArray());
    }
}